#!/usr/bin/perl

package DB::PostgresSQL;

use strict;
use warnings;

use Carp qw(croak);
use Utils qw(dd);
use DBI;

use parent 'DB::Base';

sub new
{
    my $class = shift;
    my $self = $class->SUPER::new(@_);
    return $self;
}

sub getCommand
{
    my $self = shift;
    return sprintf(
        "PGPASSWORD='%s' psql -h %s -p %s -U %s -d %s",
        $self->{_config}->{pass},
        $self->{_config}->{host},
        $self->{_config}->{port},
        $self->{_config}->{user},
        $self->{_config}->{database}
    );
}

sub connectDB
{
    my $self = shift;
    
    my $dbh = DBI->connect(
        "dbi:Pg:dbname=$self->{_config}->{database};host=$self->{_config}->{host};port=$self->{_config}->{port}",
        $self->{_config}->{user},
        $self->{_config}->{pass},
        { RaiseError => 1 }
    ) or croak "Failed to connect to database: $DBI::errstr";
    $self->{dbh} = $dbh;
}

sub getVersion
{
    my $self = shift;
    my $dbh = $self->{dbh};
    my $res = $dbh->selectrow_array("SELECT VERSION()");
    return $res;
}

sub getCount
{
    my ($self, $table) = @_;
    my $sql = sprintf($self->getSql('pg-table-count'), $self->{_config}->{schema}, $table);
    Log::debug("    Execute SQL in $self->{_config}->{user}\@$self->{_config}->{host}:$self->{_config}->{port} [ ${sql}; ]");
    my $sth = $self->{dbh}->prepare($sql);
    $sth->execute();
    my $count = $sth->fetchrow_array();
    return $count;
}

sub getNumericColumns
{
    my ($self, $table) = @_;
    my $sql = sprintf($self->getSql('gs-table-numeric-columns'), $self->{_config}->{schema}, $table);
    Log::debug("    Execute SQL in $self->{_config}->{user}\@$self->{_config}->{host}:$self->{_config}->{port} [ ${sql}; ]");
    my $sth = $self->{dbh}->prepare($sql);
    $sth->execute();
    my @columns;
    while (my $row = $sth->fetchrow_arrayref()) {
        push @columns, $row->[0];
    }
    return @columns;
}

sub getColumnSum
{
    my ($self, $table, $column) = @_;
    my $sql = sprintf($self->getSql('gs-table-column-sum'), $column, $self->{_config}->{schema}, $table);
    Log::debug("    Execute SQL in $self->{_config}->{user}\@$self->{_config}->{host}:$self->{_config}->{port} [ ${sql}; ]");
    my $sth = $self->{dbh}->prepare($sql);
    $sth->execute();
    my $sum = $sth->fetchrow_array();
    return defined $sum ? $sum : 0;
}

sub getStringColumns
{
    my ($self, $table) = @_;
    my $sql = sprintf($self->getSql('gs-table-string-columns'), $self->{_config}->{schema}, $table);
    Log::debug("    Execute SQL in $self->{_config}->{user}\@$self->{_config}->{host}:$self->{_config}->{port} [ ${sql}; ]");
    my $sth = $self->{dbh}->prepare($sql);
    $sth->execute();
    my @columns;
    while (my $row = $sth->fetchrow_arrayref()) {
        push @columns, $row->[0];
    }
    return @columns;
}

sub getAmplingConditions
{
    my $self = shift;
    my $table = shift;
    my @src_string_columns = @_;
    my $diff_row_limit = pop @src_string_columns;

    my %random_row = $self->getRandomRow($table);

    my $conditions = "";
    my $order_by = "";
    my $allowable = 0;

    if (scalar keys %random_row == 0) {
        return $allowable, $conditions;
    }

    my $sql = sprintf($self->getSql('gs-table-ampling-rows'), 'COUNT(*) AS count', $self->{_config}->{schema}, $table);
    for my $column (@src_string_columns) {
        my $column_value = $random_row{$column};
        if (defined $column_value) {
            $sql .= " AND $column = '$column_value'";
        } else {
            $sql.= " AND $column IS NULL";
        }
        Log::debug("    Execute SQL in $self->{_config}->{user}\@$self->{_config}->{host}:$self->{_config}->{port} [ ${sql}; ]");
        my $row = $self->getRow($sql);
        if ($row->{count} < $diff_row_limit) {
            if (defined $column_value) {
                $sql .= " AND $column = '$column_value'";
            } else {
                $sql.= " AND $column IS NULL";
            }
            $order_by .= ($order_by eq "" ? " " : ", ") . $column;
            $allowable = 1;
            last;
        }
    }

    return $allowable, $conditions, $order_by;
}

sub getSamplingRows
{
    my ($self, $table, $sampling_conditions, $order_by, $diff_row_limit) = @_;
    my $sql = sprintf($self->getSql('gs-table-ampling-rows'), '*', $self->{_config}->{schema}, $table). " ${sampling_conditions} ORDER BY ${order_by}";
    Log::debug("    Execute SQL in $self->{_config}->{user}\@$self->{_config}->{host}:$self->{_config}->{port} [ ${sql}; ]");
    my $sth = $self->{dbh}->prepare($sql);
    $sth->execute();
    my @rows;
    while (my $row = $sth->fetchrow_hashref()) {
        push @rows, $row;
    }

    return @rows;
}

1;